Imports System.Data.SqlClient

Friend Class SCRPT_Table
    Inherits DBBase

    Public Function DropSQL(ByVal oTable As SQLAutoDocLib.Table) As String
        Dim oOwner As New SQLAutoDocLib.Login(oTable.OwnerID)
        oOwner.Load()

        Dim sSQL As String = "drop table " & oOwner.Login & "." & oTable.Name

        Return sSQL
    End Function

    Public Function CreateSQL(ByVal oTable As SQLAutoDocLib.Table) As String
        Dim oOwner As New SQLAutoDocLib.Login(oTable.OwnerID)
        oOwner.Load()

        Dim oFieldsFactory As New SQLAutoDocLib.Field_Factory
        Dim oList As ArrayList = oFieldsFactory.GetAllFieldsForTable(oTable.ID, oTable.RevID)

        Dim oField As SQLAutoDocLib.Field = oList.Item(0)

        Dim sSQL As String = "create table " & oOwner.Login & "." & oTable.Name & "("
        sSQL = sSQL & ScriptField(oField)

        For i As Integer = 1 To oList.Count
            oField = oList.Item(i)
            sSQL = sSQL & "," & ScriptField(oField)
        Next

        sSQL = sSQL & ")"

        Return sSQL
    End Function

    Private Function ScriptField(ByVal oField As SQLAutoDocLib.Field) As String
        Dim sSQL As String = "[" & oField.Name & "] "

        With oField
            sSQL = sSQL & .Type

            If .IsIdentity Then
                sSQL = sSQL & " identity(1,1)"
            End If

            If .IsNotNull Then
                sSQL = sSQL & " not null"
            Else
                sSQL = sSQL & " null"
            End If
        End With

        Return sSQL
    End Function

    Public Function InsertSQL(ByVal oTable As SQLAutoDocLib.Table) As String
        Dim oDatabase As New SQLAutoDocLib.Database(oTable.DatabaseID, oTable.DatabaseRevID)
        oDatabase.Load()

        Dim oOwner As New SQLAutoDocLib.Login(oTable.OwnerID)
        oOwner.Load()

        '---Get all data
        Dim oData As New DataTable

        Dim oCon As New SqlConnection(oDatabase.DecryptedConnectionString)
        oCon.Open()

        Dim sSQL As String = "select * from " & oOwner.Login & ".[" & oTable.Name & "]"
        Dim oCMD As New SqlCommand(sSQL, oCon)
        Dim oDA As New SqlDataAdapter(oCMD)
        oDA.Fill(oData)
        oDA.Dispose()
        oCMD.Dispose()
        oCon.Close()
        oCon.Dispose()

        Dim sInsertBase As String = "insert into " & oOwner.Login & ".[" & oTable.Name & "] ("
        sInsertBase = sInsertBase & "[" & oData.Columns(0).ColumnName & "]"

        For i As Integer = 1 To oData.Columns.Count - 1
            Dim oColumn As System.Data.DataColumn = oData.Columns(i)
            sInsertBase = sInsertBase & ",[" & oData.Columns(i).ColumnName & "]"
        Next

        sInsertBase = sInsertBase & ") values ("

        Dim sInsertSQL As String = ""

        For Each oRow As DataRow In oData.Rows
            sInsertSQL = sInsertSQL & sInsertBase

            If oRow.Item(0) Is System.DBNull.Value Then
                sInsertSQL = sInsertSQL & "NULL"
            Else
                sInsertSQL = sInsertSQL & valuetodbvalue(oRow.Item(0))
            End If

            For i As Integer = 1 To oRow.ItemArray.GetUpperBound(0)
                If oRow.Item(i) Is System.DBNull.Value Then
                    sInsertSQL = sInsertSQL & ",NULL"
                Else
                    sInsertSQL = sInsertSQL & "," & ValueToDBValue(oRow.Item(i))
                End If
            Next

            sInsertSQL = sInsertSQL & ")" & vbCrLf
        Next

        Return sInsertSQL
    End Function

    Public Function TruncateSQL(ByVal oTable As SQLAutoDocLib.Table) As String
        Dim oOwner As New SQLAutoDocLib.Login(oTable.OwnerID)
        oOwner.Load()

        Dim sSQL As String = "truncate table " & oOwner.Login & "." & oTable.Name

        Return sSQL
    End Function
End Class
